package dao;

import bean.Status;
import util.JDBCutil;
import util.SQLUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by young on 2017/6/26.
 */
public class StateDao {
    private Connection connection = JDBCutil.getConnection();

    public List<Status> stateList() {
        List<Status> list = new ArrayList<>();
        String sql = "SELECT * FROM status";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                Status status = new Status();
                status.setId(set.getInt("id"));
                status.setState(set.getString("state"));
                list.add(status);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addStatus(Status status) {
        int row = 0;
        String sql = "INSERT INTO status (state) VALUES (?);";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, status.getState());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    public int deleteStatus(int id) {
        int row;
        String sql = "DELETE FROM status WHERE id=?";
        row = SQLUtil.deleteInfo(sql, id);
        System.out.println("row = " + row);
        return row;
    }

    public Status getStatus(int id) {
        Status status = new Status();
        String sql = "SELECT * FROM status WHERE id=?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                status.setId(set.getInt("id"));
                status.setState(set.getString("state"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return status;
    }

    public int updateStatus(Status status) {
        int row = 0;
        String sql = "UPDATE status SET state = ? WHERE id=? ;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, status.getState());
            ps.setInt(2, status.getId());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
}
